from peewee import *

db = SqliteDatabase('example.db')

class BaseModel(Model):
    class Meta:
        database = db


class User(BaseModel):
    username = CharField(unique=True, primary_key=True)  # Use CharField with primary_key=True
    email = CharField(unique=True)  


def initialize_db():
    db.connect()
    db.create_tables([User], safe=True)
    print("Database initialized and tables created.")

if __name__ == '__main__':
    initialize_db()
    
    print("=== Peewee ORM CRUD 操作示例 ===\n")
    
    # 1. 创建用户 (Create)
    print("1. 创建用户 (Create)")
    try:
        user1 = User.create(username='john_doe', email='john@example.com')
        print(f"✅ 创建用户成功: {user1.username} ({user1.email})")
        
        user2 = User.create(username='jane_smith', email='jane@example.com')
        print(f"✅ 创建用户成功: {user2.username} ({user2.email})")
        
        user3 = User.create(username='bob_wilson', email='bob@example.com')
        print(f"✅ 创建用户成功: {user3.username} ({user3.email})")
    except IntegrityError as e:
        print(f"❌ 创建用户失败 (用户已存在): {e}")
    
    print()
    
    # 2. 查询用户 (Read)
    print("2. 查询用户 (Read)")
    
    # 查询所有用户
    print("📋 所有用户:")
    users = User.select()
    for user in users:
        print(f"  - {user.username}: {user.email}")
    
    # 根据用户名查询单个用户
    print("\n🔍 查询特定用户 (john_doe):")
    try:
        user = User.get(User.username == 'john_doe')
        print(f"  找到用户: {user.username} ({user.email})")
    except User.DoesNotExist:
        print("  用户不存在")
    
    # 条件查询
    print("\n🔍 条件查询 (邮箱包含 'jane'):")
    users = User.select().where(User.email.contains('jane'))
    for user in users:
        print(f"  - {user.username}: {user.email}")
    
    print()
    
    # 3. 更新用户 (Update)
    print("3. 更新用户 (Update)")
    try:
        # 更新单个用户
        user = User.get(User.username == 'john_doe')
        user.email = 'john.doe.updated@example.com'
        user.save()
        print(f"✅ 更新用户成功: {user.username} -> {user.email}")
        
        # 批量更新
        updated_count = User.update(email=User.email + '.backup').where(User.username == 'jane_smith').execute()
        print(f"✅ 批量更新成功，影响 {updated_count} 条记录")
    except User.DoesNotExist:
        print("❌ 要更新的用户不存在")
    
    print()
    
    # 4. 删除用户 (Delete)
    print("4. 删除用户 (Delete)")
    try:
        # 删除单个用户
        user = User.get(User.username == 'bob_wilson')
        user.delete_instance()
        print(f"✅ 删除用户成功: bob_wilson")
        
        # 批量删除 (这里注释掉，避免删除太多数据)
        # deleted_count = User.delete().where(User.email.contains('backup')).execute()
        # print(f"✅ 批量删除成功，删除 {deleted_count} 条记录")
    except User.DoesNotExist:
        print("❌ 要删除的用户不存在")
    
    print()
    
    # 5. 最终状态查询
    print("5. 最终数据库状态:")
    final_users = User.select()
    print(f"📊 总用户数: {final_users.count()}")
    for user in final_users:
        print(f"  - {user.username}: {user.email}")
    
    # 关闭数据库连接
    db.close()
    print("\n🔐 数据库连接已关闭")